﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

using System.Configuration;
namespace Sanchay
{
    public class DataAccess
    {
        public SqlConnection conn;
        
        
        /// <summary>
        /// Constructor
        /// </summary>
        public DataAccess()
        {
            conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\db.mdf;Integrated Security=True;User Instance=True");
            
        }

        /// <method>
        /// Open Database Connection if Closed or Broken
        /// </method>

        public SqlConnection openConnection()
        {
            if (conn.State == ConnectionState.Closed || conn.State==ConnectionState.Broken)
            {
                conn.Open();   
            }
            return conn;
        }

        /// <summary>
        /// INSERT, UPDATE AND DELETE Query without Parameters
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>

        public int DoCommand(string query)
        {
            SqlCommand command = new SqlCommand();
            int rowsAffacted = 0;
            try
            {
                command.Connection = openConnection();
                command.CommandText = query;
                command.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                return rowsAffacted;
            }
            finally
            {
                conn.Close();
            }
            return rowsAffacted;
        }

        /// <summary>
        /// INSERT, UPDATE AND DELETE Query with Parameters
        /// </summary>
        /// <param name="query"></param>
        /// <param name="sqlParameters"></param>
        /// <returns></returns>
        public int DoCommand(string query, SqlParameter[] sqlParameters)
        {
            SqlCommand command = new SqlCommand();
            int rowsAffacted = 0;
            try
            {
                command.Connection = openConnection();
                command.CommandText = query;
                command.Parameters.AddRange(sqlParameters);
                command.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                return rowsAffacted;
            }
            finally
            {
                conn.Close();
            }
            return rowsAffacted;
        }


        /// <summary>
        /// Select Query without parameters
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public DataTable SelectCommand(string query)
        {
            SqlCommand command = new SqlCommand();
            SqlDataAdapter adpt = new SqlDataAdapter();
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                command.Connection = conn;
                command.CommandText = query;
                command.ExecuteNonQuery();
                adpt.SelectCommand = command;
                adpt.Fill(ds);
                dt = ds.Tables[0];
               
            }
            catch (SqlException e)
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        /// <summary>
        /// Select Command with Parameters
        /// </summary>
        /// <param name="query"></param>
        /// <param name="sqlParameters"></param>
        /// <returns></returns>
        public DataTable SelectCommand(string query, SqlParameter[] sqlParameters)
        {
            SqlCommand command = new SqlCommand();
            SqlDataAdapter adpt = new SqlDataAdapter();
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            try
            {
                command.Connection = openConnection();
                command.CommandText = query;
                command.Parameters.AddRange(sqlParameters);
                command.ExecuteNonQuery();
                adpt.SelectCommand = command;
                adpt.Fill(ds);
                dt = ds.Tables[0];
            }
            catch (SqlException e)
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        
    }
}
